/*
 * Copyright (c) 2010-2020 DMGIS Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * DMGIS. You shall not disclose such Confidential Information
 * and shall use it only in accordance with the terms of the agreements
 * you entered into with DMGIS.
 *
 */
package com.dmgis.qxfw.dataservice.pages.service.impl;

import com.dmgis.qxfw.dataservice.businessservice.api.domain.DbConfig;
import com.dmgis.qxfw.dataservice.businessservice.api.domain.RequestDataBySelect;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.ny.Coordinator;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.ny.Infocollector;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.xt.login;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.xt.roles;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.xt.users;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.zy.sys_user;
import com.dmgis.qxfw.dataservice.businessservice.api.entity.zy.user_table;
import com.dmgis.qxfw.dataservice.businessservice.common.util.JsonUtil;
import com.dmgis.qxfw.dataservice.businessservice.common.util.TokenProccessor;
import com.dmgis.qxfw.dataservice.businessservice.controller.ProcessRequestController;
import com.dmgis.qxfw.dataservice.pages.entity.*;
import com.dmgis.qxfw.dataservice.pages.service.ILoginService;
import com.fasterxml.jackson.core.type.TypeReference;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

@Service("LoginService")
public class LoginServiceImpl implements ILoginService {
    ProcessRequestController pc = new ProcessRequestController();
    private static final Logger log = Logger.getLogger(ComDisplayServiceImpl.class);

    public List<login> CheckLogin(String userName, String passWord) {
        // session.invalidate();// 清空session
        String result = "";
        String StrRole = "";
        List<login> strResult = new ArrayList<login>();
        login gin = new login();
        String password = "";
        List<user_table> listData = new ArrayList<user_table>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
        strWhere.append(String.format("username='%s'", userName));
        //strWhere.append("and servicewebaddress like '%yyproweb%'");
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "user_table");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        // 用户不存在
        if (listData.size() == 0) {
            result = "-1";

        } else {
            // 判断密码
            try {
                selectParam.remove("param");
                selectRequest = new RequestDataBySelect();
                selectRequest.setParam(String.format("username='%s' ", userName) + " and servicewebaddress like '%ylproweb%'");
                selectParam.put("param", JsonUtil.object2Json(selectRequest));
                String message = pc.processRequest(selectParam);
                password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                }).get(0).getPassword();
                listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                });
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
            if (password.equals(passWord)) {

                try {
                    selectRequest = new RequestDataBySelect();
                    selectParam = new HashMap<String, String>();
                    selectParam.put("method", "roles");
                    selectParam.put("sqlType", "select");
                    selectParam.put("advCode", DbConfig.advCode);
                    selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    String message = pc.processRequest(selectParam);
                    List<roles> listRoles = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<roles>>() {
                    });
                    Map<String, String> Roles = new HashMap<String, String>();
                    if (listRoles.size() > 0) {
                        for (int i = 0; i < listRoles.size(); i++) {
                            Roles.put(listRoles.get(i).getId().toString(), listRoles.get(i).getRole());
                        }
                    }
                    //判断权限
                    selectParam.remove("param");
                    selectParam.remove("method");
                    selectRequest = new RequestDataBySelect();
                    selectParam.put("method", "user_table");
                    selectParam.put("advCode", DbConfig.advCode);
                    selectRequest.setParam(String.format("username='%s' ", userName) + " and " + String.format("password='%s' ", passWord) + " and servicewebaddress like '%ylproweb%'");

                    selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    message = pc.processRequest(selectParam);
                    password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    }).get(0).getPassword();
                    listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    });
                    boolean isPersimission = false;
                    String newUrl = listData.get(0).getServicewebaddress();
                    if (newUrl.contains("ylgt"))    //国土用户 -地灾
                    {
                        if (listData.size() > 0) {

                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();

                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("自然资源") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_dizhizh.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("ylyj"))   //应急用户
                    {
                        if (listData.size() > 0) {

                                    for (int i = 0; i < listData.size(); i++) {
                                        String permission = listData.get(i).getPermission();

                                        for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                            if (entry.getKey().equals(permission) && entry.getValue().contains("应急") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_yingjiqx.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("ylsl"))     //水利用户
                    {
                        if (listData.size() > 0) {

                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();

                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("水利") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("pages/zyinfo/ggqx_zxfw_shanhongfx.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("ylly"))        //林业用户
                    {
                        if (listData.size() > 0) {
                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();

                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("林业") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_senlinhx.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("ylhb"))      //环保用户
                    {
//                        if (listData.size() > 0) {
//                            for (int i = 0; i < listData.size(); i++) {
//                                String permission = listData.get(i).getPermission();
//
//                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
//                                    if (entry.getKey().equals(permission) && entry.getValue().contains("环保") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
//                                        isPersimission = true;
//                                        StrRole = entry.getValue();
//                                        break;
//                                    }
//                                }
//                                gin.setAppUrl("/pages/zyinfo/huanbao_index.html");
//                                if (isPersimission) break;
//                            }
//                            if (!isPersimission) result = "-3";
//                        }
                    } else if (newUrl.contains("yljt"))        //交通用户
                    {
                        if (listData.size() > 0) {
                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();

                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("交通") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_jiaotongqx.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("ylrb")) {
//                        if (listData.size() > 0) {
//                            for (int i = 0; i < listData.size(); i++) {
//                                String permission = listData.get(i).getPermission();
//                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
//                                    if (entry.getKey().equals(permission) && entry.getValue().contains("保险") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
//                                        isPersimission = true;
//                                        StrRole = entry.getValue();
//                                        break;
//                                    }
//                                }
//                                if (isPersimission) break;
//                            }
//                            if (!isPersimission) result = "-3";
//                        }
                    } else if (newUrl.contains("yltour"))        //旅游用户
                    {
                        if (listData.size() > 0) {
                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();

                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("旅游") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_lvyouqx.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("yldl"))        //电力用户
                    {
                        if (listData.size() > 0) {
                            for (int i = 0; i < listData.size(); i++) {
                                String permission = listData.get(i).getPermission();
                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                    if (entry.getKey().equals(permission) && entry.getValue().contains("电力") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                        isPersimission = true;
                                        StrRole = entry.getValue();
                                        break;
                                    }
                                }
                                gin.setAppUrl("/pages/zyinfo/dianli_index.html");
                                if (isPersimission) break;
                            }
                            if (!isPersimission) result = "-3";
                        }
                    } else if (newUrl.contains("yltt"))        //电力用户
                    {
                       if (listData.size() > 0) {
                           for (int i = 0; i < listData.size(); i++) {
                               String permission = listData.get(i).getPermission();
                               for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                   if (entry.getKey().equals(permission) && entry.getValue().contains("铁塔") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                       isPersimission = true;
                                       StrRole = entry.getValue();
                                       break;
                                   }
                               }
                               if (isPersimission) break;
                           }
                           if (!isPersimission) result = "-3";
                       }
                    } else if (newUrl.contains("ylbx"))       //保险行业
                    {
                       if (listData.size() > 0) {
                           for (int i = 0; i < listData.size(); i++) {
                               String permission = listData.get(i).getPermission();
                               for (Map.Entry<String, String> entry : Roles.entrySet()) {
                                   if (entry.getKey().equals(permission) && entry.getValue().contains("保险") || entry.getKey().equals(permission) && entry.getValue().contains("管理")) {
                                       isPersimission = true;
                                       StrRole = entry.getValue();
                                       break;
                                   }
                               }
                               gin.setAppUrl("/pages/zyinfo/zhongbao_index.html");
                               if (isPersimission) break;
                           }
                           if (!isPersimission) result = "-3";
                       }
                    } else if (newUrl.contains("ylnl"))        //市政用户，城市内涝
                    {
//                        if (listData.size() > 0) {
//
//                            for (int i = 0; i < listData.size(); i++) {
//                                String permission = listData.get(i).getPermission();
//
//                                for (Map.Entry<String, String> entry : Roles.entrySet()) {
//                                    if ((entry.getKey().equals(permission) && entry.getValue().contains("市政")) || (entry.getKey().equals(permission) && entry.getValue().contains("管理"))) {
//                                        isPersimission = true;
//                                        StrRole = entry.getValue();
//                                        break;
//                                    }
//                                }
//                                gin.setAppUrl("/pages/zyinfo/ggqx_zxfw_csnl.html");
//                                if (isPersimission) break;
//                            }
//                            if (!isPersimission) result = "-3";
//                        }
                    }
                    if (isPersimission) {
                        String address = listData.get(0).getServicewebaddress();
                        if (!listData.get(0).getReservoirname().contains("水库") && !(listData.get(0).getReservoirname().contains("水电站")))
                            result = address;
                        else {
                            if (address.endsWith("/"))
                                result = address.substring(0, address.length() - 1) + "&username=" + listData.get(0).getReservoirname() + "/";
                            else
                                result = address + "&username=" + listData.get(0).getReservoirname() + "/";
                        }
                        gin.setReservoirid(listData.get(0).getReservoirid());
                        gin.setReservoirname(listData.get(0).getReservoirname());
                        gin.setDrainagearea(listData.get(0).getDrainagearea());
                        gin.setCounty(listData.get(0).getCounty());
                    }
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            } else {
                result = "-2";
            }
        }
        gin.setUrl(result);
        TokenProccessor tokenProccessor = new TokenProccessor();
        gin.setToken(tokenProccessor.generateToken(userName));
        gin.setStrRole(StrRole);
        if (listData != null && listData.size() > 0) {
            gin.setDrainagearea(listData.get(0).getDrainagearea());
            gin.setUsername(listData.get(0).getUsername());
        }
        strResult.add(gin);
        return strResult;
    }

    @Override
    public List<login> CheckLoginNy(String userName, String passWord, String role) {
        String result = "";
        List<login> strResult = new ArrayList<login>();
        login gin = new login();
        List<users> listData = new ArrayList<users>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        String[] columns = new String[]{"distinct username", "type", "ownercity", "ownercounty", "ownertown", "ownervillage", "stationid"};
        selectRequest.setColumns(columns);
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
        strWhere.append(String.format("username='%s'", userName));
        strWhere.append(String.format("and password = '%s'", passWord));
        strWhere.append(String.format("and type = '%s'", role));
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "users");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<users>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        if (listData.size() == 0) {
            result = "-4";
            gin.setUrl(result);
            strResult.add(gin);
            return strResult;
        }
        users users = listData.get(0);
        TokenProccessor tokenProccessor = new TokenProccessor();
        gin.setToken(tokenProccessor.generateToken(userName));
        gin.setStrRole(users.getRole());
        gin.setUrl(result);
        gin.setUsername(users.getUsername());
        gin.setUsertype(users.getType());
        strResult.add(gin);
        return strResult;
    }

    @Override
    public List<login> CheckLoginInfo(String userName, String passWord, String role) {
        String result = "";
        List<login> strResult = new ArrayList<login>();
        login gin = new login();
        List<Infocollector> listData = new ArrayList<Infocollector>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        String[] columns = new String[]{"distinct infocollectorname",
                "stationid"};
        selectRequest.setColumns(columns);
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
        strWhere.append(String.format("infocollectorname='%s'", userName));
        strWhere.append(String.format("and infocollectorpwd = '%s'", passWord));
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "infocollector");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<Infocollector>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        if (listData.size() == 0) {
            result = "-4";
            gin.setUrl(result);
            strResult.add(gin);
            return strResult;
        }
        Infocollector infocollector = listData.get(0);
        TokenProccessor tokenProccessor = new TokenProccessor();
        gin.setToken(tokenProccessor.generateToken(userName));
        gin.setUrl(result);
        gin.setUsername(infocollector.getInfocollectorname());
        gin.setUsertype(role);
        strResult.add(gin);
        return strResult;
    }

    @Override
    public List<login> CheckLoginHelp(String userName, String passWord, String role) {
        String result = "";
        List<login> strResult = new ArrayList<login>();
        login gin = new login();
        List<Coordinator> listData = new ArrayList<Coordinator>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        String[] columns = new String[]{"distinct coordinatename","stationid"};
        selectRequest.setColumns(columns);
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
        strWhere.append(String.format("coordinatename='%s'", userName));
        strWhere.append(String.format("and coordinatepwd = '%s'", passWord));
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "coordinator");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<Coordinator>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        if (listData.size() == 0) {
            result = "-4";
            gin.setUrl(result);
            strResult.add(gin);
            return strResult;
        }
        Coordinator coordinator = listData.get(0);
        TokenProccessor tokenProccessor = new TokenProccessor();
        gin.setToken(tokenProccessor.generateToken(userName));
        gin.setUrl(result);
        gin.setUsername(coordinator.getCoordinatename());
        gin.setUsertype(role);
        strResult.add(gin);
        return strResult;
    }

    @Override
    public List<login> CheckLogin1(String userName, String passWord) {
        // session.invalidate();// 清空session
        String result = "";
        String StrRole = "";
        List<login> strResult = new ArrayList<login>();
        login gin = new login();
        String password = "";
        String advName = "";
        List<user_table> listData = new ArrayList<user_table>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
        strWhere.append(String.format("username='%s'", userName));
        //strWhere.append("and servicewebaddress like '%yyproweb%'");
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "user_table");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        // 用户不存在
        if (listData.size() == 0) {
            result = "-1";

        } else {
            // 判断密码
            try {
                selectParam.remove("param");
                selectRequest = new RequestDataBySelect();
                selectRequest.setParam(String.format("username='%s' ", userName) );
                selectParam.put("param", JsonUtil.object2Json(selectRequest));
                String message = pc.processRequest(selectParam);
                password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                }).get(0).getPassword();
                listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                });
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
            if (password.equals(passWord)) {

                try {
                    selectRequest = new RequestDataBySelect();
                    selectParam = new HashMap<String, String>();
                    selectParam.put("method", "roles");
                    selectParam.put("sqlType", "select");
                    selectParam.put("advCode", DbConfig.advCode);
                    selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    String message = pc.processRequest(selectParam);
                    List<roles> listRoles = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<roles>>() {
                    });
                    Map<String, String> Roles = new HashMap<String, String>();
                    if (listRoles.size() > 0) {
                        for (int i = 0; i < listRoles.size(); i++) {
                            Roles.put(listRoles.get(i).getId().toString(), listRoles.get(i).getRole());
                        }
                    }
                    //判断权限
                    selectParam.remove("param");
                    selectParam.remove("method");
                    selectRequest = new RequestDataBySelect();
                    selectParam.put("method", "user_table");
                    selectParam.put("advCode", DbConfig.advCode);
                    selectRequest.setParam(String.format("username='%s' ", userName) + " and " + String.format("password='%s' ", passWord) );

                    selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    message = pc.processRequest(selectParam);
                    password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    }).get(0).getPassword();
                    advName = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    }).get(0).getCounty();
                    gin.setAdvName(advName);
                    listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    });
                    boolean isPersimission = false;

                    if (isPersimission) {
                        String address = listData.get(0).getServicewebaddress();
                        if (!listData.get(0).getReservoirname().contains("水库") && !(listData.get(0).getReservoirname().contains("水电站")))
                            result = address;
                        else {
                            if (address.endsWith("/"))
                                result = address.substring(0, address.length() - 1) + "&username=" + listData.get(0).getReservoirname() + "/";
                            else
                                result = address + "&username=" + listData.get(0).getReservoirname() + "/";
                        }
                        gin.setReservoirid(listData.get(0).getReservoirid());
                        gin.setReservoirname(listData.get(0).getReservoirname());
                        gin.setDrainagearea(listData.get(0).getDrainagearea());
                        gin.setCounty(listData.get(0).getCounty());
                    }
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            } else {
                result = "-2";
            }
        }
        gin.setUrl(result);
        TokenProccessor tokenProccessor = new TokenProccessor();
        gin.setToken(tokenProccessor.generateToken(userName));
        gin.setStrRole(StrRole);
        String advCode = "";
        switch (userName){
            case "yili":
                advCode = "654000";
                break;
            case "huoerguosi":
                advCode = "654004";
                break;
            case "caxian":
                advCode = "654022";
                break;
            case "huocheng":
                advCode = "654023";
                break;
            case "tekesi":
                advCode = "654027";
                break;
            case "yining":
                advCode = "654002";
                break;
            case "yiningxian":
                advCode = "654021";
                break;
            case "gongliu":
                advCode = "654024";
                break;
            case "xinyuan":
                advCode = "654025";
                break;
            case "nileke":
                advCode = "654028";
                break;
            case "kuitun":
                advCode = "654003";
                break;
            case "zhaosu":
                advCode = "654026";
                break;
            default:
                advCode = "";
                break;
        }
        gin.setAdvCode(advCode);
        if (listData != null && listData.size() > 0) {
            gin.setDrainagearea(listData.get(0).getDrainagearea());
            gin.setUsername(listData.get(0).getUsername());
        }
        strResult.add(gin);
        return strResult;
    }
    @Override
    public List<SysUser> CheckLogin2(String userName, String passWord) {
        // session.invalidate();// 清空session
        String result = "";
        String StrRole = "";
        List<SysUser> strResult = new ArrayList<SysUser>();
        //login gin = new login();
        SysUser sysUser = new SysUser();
        String role = "";
        String password = "";
        String advName = "";
        List<User> listData = new ArrayList<User>();
        // 判断用户名是否存在
        Map<String, String> selectParam = new HashMap<String, String>();
        RequestDataBySelect selectRequest = new RequestDataBySelect();
        // 拼接where字段
        StringBuilder strWhere = new StringBuilder();
         strWhere.append(String.format("user_account='%s'", userName));
        //strWhere.append("and servicewebaddress like '%yyproweb%'");
        selectRequest.setParam(strWhere.toString());
        try {
            selectParam.put("method", "sys_user");
            selectParam.put("sqlType", "select");
            selectParam.put("advCode", DbConfig.advCode);
            selectParam.put("param", JsonUtil.object2Json(selectRequest));
            String message = pc.processRequest(selectParam);
            listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<User>>() {
            });
        } catch (Exception e) {
            // TODO: handle exception
        }
        // 用户不存在
        if (listData.size() == 0) {
            result = "-1";

        } else {
            // 判断密码
            try {
                selectParam.remove("param");
                selectRequest = new RequestDataBySelect();
                selectRequest.setParam(String.format("user_account='%s' ", userName) );
                selectParam.put("param", JsonUtil.object2Json(selectRequest));
                String message = pc.processRequest(selectParam);
                password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<User>>() {
                }).get(0).getUser_password();
                listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<User>>() {
                });
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
            if (password.equals(passWord)) {
                try {
                    // selectRequest = new RequestDataBySelect();
                    // selectParam = new HashMap<String, String>();
                    // selectParam.put("method", "roles");
                    // selectParam.put("sqlType", "select");
                    // selectParam.put("advCode", DbConfig.advCode);
                    // selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    // String message = pc.processRequest(selectParam);
                    // List<roles> listRoles = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<roles>>() {
                    // });
                    // Map<String, String> Roles = new HashMap<String, String>();
                    // if (listRoles.size() > 0) {
                    //     for (int i = 0; i < listRoles.size(); i++) {
                    //         Roles.put(listRoles.get(i).getId().toString(), listRoles.get(i).getRole());
                    //     }
                    // }
                    //判断权限
                    // selectParam.remove("param");
                    // selectParam.remove("method");
                    // selectRequest = new RequestDataBySelect();
                    // selectParam.put("method", "user_table");
                    // selectParam.put("advCode", DbConfig.advCode);
                    // selectRequest.setParam(String.format("user_account='%s' ", userName) + " and " + String.format("user_password='%s' ", passWord) );
                    //
                    // selectParam.put("param", JsonUtil.object2Json(selectRequest));
                    // message = pc.processRequest(selectParam);
                    // password = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    // }).get(0).getPassword();
                    // advName = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    // }).get(0).getCounty();
                    // gin.setAdvName(advName);
                    // listData = JsonUtil.jsonStr2TypeReference(message, new TypeReference<List<user_table>>() {
                    // });
                    // boolean isPersimission = false;
                    //
                    // if (isPersimission) {
                    //     String address = listData.get(0).getServicewebaddress();
                    //     if (!listData.get(0).getReservoirname().contains("水库") && !(listData.get(0).getReservoirname().contains("水电站")))
                    //         result = address;
                    //     else {
                    //         if (address.endsWith("/"))
                    //             result = address.substring(0, address.length() - 1) + "&username=" + listData.get(0).getReservoirname() + "/";
                    //         else
                    //             result = address + "&username=" + listData.get(0).getReservoirname() + "/";
                    //     }
                    //     gin.setReservoirid(listData.get(0).getReservoirid());
                    //     gin.setReservoirname(listData.get(0).getReservoirname());
                    //     gin.setDrainagearea(listData.get(0).getDrainagearea());
                    //     gin.setCounty(listData.get(0).getCounty());
                    //}
                    sysUser.setId(listData.get(0).getId());
                    sysUser.setUserName(listData.get(0).getUser_account());
                    sysUser.setAdvName(listData.get(0).getAdvName());
                    sysUser.setAdvCode(listData.get(0).getAdvCode());
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            } else {
                result = "-2";
            }
        }
        sysUser.setUrl(result);
        TokenProccessor tokenProccessor = new TokenProccessor();
        sysUser.setToken(tokenProccessor.generateToken(userName));

        // if (listData != null && listData.size() > 0) {
        //     sysUser.setUserName(listData.get(0).getUser_name());
        // }
        strResult.add(sysUser);
        return strResult;
    }

    @Override
    public PageInfo<SysUser> selectLikeUser(String userName, String useraccount,int pageNum, int pageSize) throws Exception {
         StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT id,user_account,user_name,unit,user_lv,user_password FROM sys_user WHERE 1=1 ");
        if (useraccount != null && !useraccount.isEmpty()) {
            stringBuilder.append(" AND user_account LIKE '%" + useraccount + "%'");
        }
        if (userName != null && !userName.isEmpty()) {
            stringBuilder.append(" AND user_name LIKE '%" + userName + "%'");
        }
        String sql = stringBuilder.toString();
        List<SysUser> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                SysUser sysUser = new SysUser();
                sysUser.setId(!Objects.equals(resultSet.getInt("id"), "-65535") ? resultSet.getInt("id") : null);
                sysUser.setUserAccount(!Objects.equals(resultSet.getString("user_account"), "-65535") ?
                        resultSet.getString("user_account") : null);
                sysUser.setUserName(!Objects.equals(resultSet.getString("user_name"), "-65535") ?
                        resultSet.getString("user_name") : null);
                sysUser.setUnit(!Objects.equals(resultSet.getString("unit"), "-65535") ?
                        resultSet.getString("unit") : null);
                sysUser.setUserLv(!Objects.equals(resultSet.getString("user_lv"), "-65535") ?
                        resultSet.getString("user_lv") : null);
                sysUser.setPassword(!Objects.equals(resultSet.getString("user_password"), "-65535") ?
                        resultSet.getString("user_password") : null);

                result.add(sysUser);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return new PageInfo<>(result);

    }

    @Override
    public PageInfo<SysUser> selectAllUser(int page, int size) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT id,user_account,user_name,unit,user_lv,user_password FROM sys_user");
        String sql = stringBuilder.toString();
        List<SysUser> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                SysUser sysUser = new SysUser();
                sysUser.setId(!Objects.equals(resultSet.getInt("id"), "-65535") ? resultSet.getInt("id") : null);
                sysUser.setUserAccount(!Objects.equals(resultSet.getString("user_account"), "-65535") ?
                        resultSet.getString("user_account") : null);
                sysUser.setUserName(!Objects.equals(resultSet.getString("user_name"), "-65535") ?
                        resultSet.getString("user_name") : null);
                sysUser.setUnit(!Objects.equals(resultSet.getString("unit"), "-65535") ?
                        resultSet.getString("unit") : null);
                sysUser.setUserLv(!Objects.equals(resultSet.getString("user_lv"), "-65535") ?
                        resultSet.getString("user_lv") : null);
                sysUser.setPassword(!Objects.equals(resultSet.getString("user_password"), "-65535") ?
                        resultSet.getString("user_password") : null);

                result.add(sysUser);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return new PageInfo<>(result);
    }

    @Override
    public int updateUserForId(String userName, String userAccount, String userLv, String id, String password, String unit) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        // 构建 SQL 更新语句
        stringBuilder.append("UPDATE sys_user SET user_account = '").append(userAccount)
                .append("', user_name = '").append(userName)
                .append("', unit = '").append(unit)
                .append("', user_lv = '").append(userLv)
                .append("', user_password = '").append(password)
                .append("' WHERE id = '").append(id).append("'");

        String sql = stringBuilder.toString();

        int result = 0; // 用于存储更新影响的行数

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1(); // 获取数据库连接
             Statement statement = connection.createStatement()) {

            // 执行 SQL 更新语句并获取影响的行数
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // 异常处理：可以打印日志或抛出自定义异常
            e.printStackTrace();
            throw new Exception("更新失败", e);
        }

        // 返回影响的行数
        return result;
    }

    @Override
    public int deleteUserForId(String id)throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        // 构建 SQL 更新语句
        stringBuilder.append("DELETE FROM sys_user WHERE id = '"+id+"'");

        String sql = stringBuilder.toString();
        int result = 0; // 用于存储更新影响的行数
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1(); // 获取数据库连接
             Statement statement = connection.createStatement()) {

            // 执行 SQL 更新语句并获取影响的行数
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // 异常处理：可以打印日志或抛出自定义异常
            e.printStackTrace();
            throw new Exception("删除失败", e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 返回影响的行数
        return result;
    }

    @Override
    public int insertUserInfo(String userName, String userAccount, String userLv, String password, String unit) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        // 构建 SQL 更新语句
        stringBuilder.append("INSERT INTO sys_user (user_account,user_name,user_password,unit,user_lv) " +
                " VALUES ('"+userAccount+"','"+userName+"','"+password+"','"+unit+"','"+userLv+"');");

        String sql = stringBuilder.toString();
        int result = 0; // 用于存储更新影响的行数
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1(); // 获取数据库连接
             Statement statement = connection.createStatement()) {

            // 执行 SQL 更新语句并获取影响的行数
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // 异常处理：可以打印日志或抛出自定义异常
            e.printStackTrace();
            throw new Exception("插入失败", e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 返回影响的行数
        return result;
    }

    @Override
    public List<Permission> selectAllPermission(String type) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT p1.id AS level1_id, p1.title AS level1_title, \n" +
                "    p2.id AS level2_id, p2.title AS level2_title,\n" +
                "    p3.id AS level3_id, p3.title AS level3_title\n" +
                "FROM sys_permission p1 " +
                "LEFT JOIN sys_permission p2 ON p1.id = p2.parent_id\n" +
                "LEFT JOIN sys_permission p3 ON p2.id = p3.parent_id\n" +
                "WHERE p1.leven = 1 AND p2.leven = 2 AND p3.leven = 3 AND p1.type = '"+type+"';");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel1Title( !Objects.equals(resultSet.getString("level1_title"), "-65535") ?
                        resultSet.getString("level1_title") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel2Title(!Objects.equals(resultSet.getString("level2_title"), "-65535") ?
                        resultSet.getString("level2_title") : null);
                permission.setLevel3Id(!Objects.equals(resultSet.getString("level3_id"), "-65535") ?
                        resultSet.getInt("level3_id") : null);
                permission.setLevel3Title(!Objects.equals(resultSet.getString("level3_title"), "-65535") ?
                        resultSet.getString("level3_title") : null);
                result.add(permission);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public int insertUserPermission(String userId, List<Integer> permissionIdList) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        // 构建 SQL 插入语句
        // 我们要构建多条INSERT语句用于批量插入
        stringBuilder.append("INSERT INTO sys_user_permission (user_id, permission_id) VALUES ");

        // 遍历 permissionIdList，构建每条插入语句的VALUES部分
        for (int i = 0; i < permissionIdList.size(); i++) {
            stringBuilder.append("('").append(userId).append("', ").append(permissionIdList.get(i)).append(")");
            // 如果不是最后一个权限，添加逗号
            if (i < permissionIdList.size() - 1) {
                stringBuilder.append(", ");
            }
        }
        // 生成的完整SQL语句
        String sql = stringBuilder.toString();
        int result = 0;  // 用于存储更新影响的行数

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1(); // 获取数据库连接
             Statement statement = connection.createStatement()) {

            // 执行 SQL 插入语句并获取影响的行数
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // 异常处理：可以打印日志或抛出自定义异常
            e.printStackTrace();
            throw new Exception("插入失败", e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 返回影响的行数
        return result;
    }

    @Override
    public List<Permission> selectHavePermission(String id) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT 1 AS level1_id,'预警预报一级' AS level1_title,p1.id AS level3_id, " +
                "p1.title AS level3_title,p2.id AS level2_id,p2.title AS level2_title,sup.id AS user_permission_id\n" +
                "FROM sys_permission p1 " +
                "LEFT JOIN sys_permission p2 ON p1.parent_id = p2.id\n" +
                "JOIN sys_user_permission sup ON p1.id = sup.permission_id\n" +
                "WHERE sup.user_id = '"+id+"'  AND P1.type = '0'  ORDER BY sup.id ASC;");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel1Title( !Objects.equals(resultSet.getString("level1_title"), "-65535") ?
                        resultSet.getString("level1_title") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel2Title(!Objects.equals(resultSet.getString("level2_title"), "-65535") ?
                        resultSet.getString("level2_title") : null);
                permission.setLevel3Id(!Objects.equals(resultSet.getString("level3_id"), "-65535") ?
                        resultSet.getInt("level3_id") : null);
                permission.setLevel3Title(!Objects.equals(resultSet.getString("level3_title"), "-65535") ?
                        resultSet.getString("level3_title") : null);
                permission.setUserPermissionId(!Objects.equals(resultSet.getString("user_permission_id"), "-65535") ?
                        resultSet.getInt("user_permission_id") : null);
                result.add(permission);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public ArrayList<Integer> selectPermission(String id,String type) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
      stringBuilder.append("SELECT p2.permission_id FROM sys_user_permission p2 LEFT JOIN sys_permission p1 ON p1.id = p2.permission_id WHERE p2.user_id = '"+id+"' AND p1.type = '"+type+"' ;");
        String sql = stringBuilder.toString();
        ArrayList<Integer> result = new ArrayList<>();
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                result.add(resultSet.getInt("permission_id"));
            }
            return result;

        }
    }

    @Override
    public int deleteUserPermissionForId(String userId, List<Integer> deletePermissionIdList) throws Exception {
        StringBuilder permissionIds = new StringBuilder();
        for (int i = 0; i < deletePermissionIdList.size(); i++) {
            permissionIds.append("'").append(deletePermissionIdList.get(i)).append("'");
            if (i < deletePermissionIdList.size() - 1) {
                permissionIds.append(", ");
            }
        }

// 构建 SQL 删除语句
        String sql = "DELETE FROM sys_user_permission WHERE user_id IN ('" + userId + "') AND permission_id IN (" + permissionIds.toString() + ")";
        int result = 0; // 用于存储更新影响的行数
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1(); // 获取数据库连接
             Statement statement = connection.createStatement()) {

            // 执行 SQL 更新语句并获取影响的行数
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // 异常处理：可以打印日志或抛出自定义异常
            e.printStackTrace();
            throw new Exception("删除失败", e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 返回影响的行数
        return result;
    }

    @Override
    public List<Permission> selectAllPermissionByType(String type) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT p1.ID AS level1_id,p1.title AS level1_title,p2.ID AS level2_id,p2.title AS level2_title " +
                "FROM sys_permission p1 LEFT JOIN sys_permission p2 ON p1.ID = p2.parent_id WHERE p1.leven = 1 " +
                "   AND p2.leven = 2 AND p1.type = '"+type+"';");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel1Title( !Objects.equals(resultSet.getString("level1_title"), "-65535") ?
                        resultSet.getString("level1_title") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel2Title(!Objects.equals(resultSet.getString("level2_title"), "-65535") ?
                        resultSet.getString("level2_title") : null);
                result.add(permission);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Permission> selectHavePermissionByType(String id) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT p1.id AS level2_id, p1.title AS level2_title,p2.id AS level1_id,p2.title AS level1_title,sup.id AS user_permission_id "+
                "FROM sys_permission p1 LEFT JOIN sys_permission p2 ON p1.parent_id = p2.id JOIN sys_user_permission sup ON p1.id = sup.permission_id "+
                "WHERE sup.user_id = '"+id+"'  AND P1.type = '1'  ORDER BY sup.id ASC;");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel1Title( !Objects.equals(resultSet.getString("level1_title"), "-65535") ?
                        resultSet.getString("level1_title") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel2Title(!Objects.equals(resultSet.getString("level2_title"), "-65535") ?
                        resultSet.getString("level2_title") : null);
                permission.setUserPermissionId(!Objects.equals(resultSet.getString("user_permission_id"), "-65535") ?
                        resultSet.getInt("user_permission_id") : null);
                result.add(permission);

            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Permission> selectHavePermissionByApp(String id) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT p1.id AS level2_id,p2.id AS level1_id,p1.path AS level2_href, p1.title AS level2_text,p1.icon AS level2_image,p2.path AS level1_src,p2.title AS level1_name,p2.component AS level1_src1,p2.icon AS level1_image,sup.id AS user_permission_id  "+
                "FROM sys_permission p1 LEFT JOIN sys_permission p2 ON p1.parent_id = p2.id JOIN sys_user_permission sup ON p1.id = sup.permission_id  "+
                "WHERE sup.user_id = '"+id+"'  AND P1.type = '1'  ORDER BY sup.id ASC;");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel1Src(!Objects.equals(resultSet.getString("level1_src"), "-65535") ?
                        resultSet.getString("level1_src") : null);
                permission.setLevel1Src1( !Objects.equals(resultSet.getString("level1_src1"), "-65535") ?
                        resultSet.getString("level1_src1") : null);
                permission.setLevel1Name(!Objects.equals(resultSet.getString("level1_name"), "-65535") ?
                        resultSet.getString("level1_name") : null);
                permission.setLevel1Image(!Objects.equals(resultSet.getString("level1_image"), "-65535") ?
                        resultSet.getString("level1_image") : null);
                permission.setLevel2Text(!Objects.equals(resultSet.getString("level2_text"), "-65535") ?
                        resultSet.getString("level2_text") : null);
                permission.setLevel2Image(!Objects.equals(resultSet.getString("level2_image"), "-65535") ?
                        resultSet.getString("level2_image") : null);
                permission.setLevel2Href(!Objects.equals(resultSet.getString("level2_href"), "-65535") ?
                        resultSet.getString("level2_href") : null);
                permission.setUserPermissionId(!Objects.equals(resultSet.getString("user_permission_id"), "-65535") ?
                        resultSet.getInt("user_permission_id") : null);
                result.add(permission);

            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Permission> selectHavePermissionBy(String id) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT 1 AS level1_id,'预警预报一级' AS level1_title,p1.id AS level3_id,p1.icon AS level3_icon,p2.icon AS level2_icon, " +
                "p1.title AS level3_title,p2.id AS level2_id,p2.title AS level2_title,sup.id AS user_permission_id\n" +
                "FROM sys_permission p1 " +
                "LEFT JOIN sys_permission p2 ON p1.parent_id = p2.id\n" +
                "JOIN sys_user_permission sup ON p1.id = sup.permission_id\n" +
                "WHERE sup.user_id = '"+id+"'  AND P1.type = '0'  ORDER BY sup.id ASC;");
        String sql = stringBuilder.toString();
        List<Permission> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 处理查询结果
            while (resultSet.next()) {
                Permission permission = new Permission();
                permission.setLevel1Id(!Objects.equals(resultSet.getString("level1_id"), "-65535") ?
                        resultSet.getInt("level1_id") : null);
                permission.setLevel1Title( !Objects.equals(resultSet.getString("level1_title"), "-65535") ?
                        resultSet.getString("level1_title") : null);
                permission.setLevel2Id(!Objects.equals(resultSet.getString("level2_id"), "-65535") ?
                        resultSet.getInt("level2_id") : null);
                permission.setLevel2Title(!Objects.equals(resultSet.getString("level2_title"), "-65535") ?
                        resultSet.getString("level2_title") : null);
                permission.setLevel2Image(!Objects.equals(resultSet.getString("level2_icon"), "-65535") ?
                        resultSet.getString("level2_icon") : null);
                permission.setLevel3Image(!Objects.equals(resultSet.getString("level3_icon"), "-65535") ?
                        resultSet.getString("level3_icon") : null);
                permission.setLevel3Id(!Objects.equals(resultSet.getString("level3_id"), "-65535") ?
                        resultSet.getInt("level3_id") : null);
                permission.setLevel3Title(!Objects.equals(resultSet.getString("level3_title"), "-65535") ?
                        resultSet.getString("level3_title") : null);
                permission.setUserPermissionId(!Objects.equals(resultSet.getString("user_permission_id"), "-65535") ?
                        resultSet.getInt("user_permission_id") : null);
                result.add(permission);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }


    private static Connection getConnection() throws SQLException, ClassNotFoundException, IOException {
        try {
            Properties prop = new Properties();
            InputStream in = ComDisplayServiceImpl.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
            Connection connection;
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(prop.getProperty("postgresql.master.url"), prop.getProperty("postgresql.username"), prop.getProperty("postgresql.password"));

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
    }
    private static Connection getConnection1() throws SQLException, ClassNotFoundException, IOException {
        try {
            Properties prop = new Properties();
            InputStream in = ComDisplayServiceImpl.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
            Connection connection;
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(prop.getProperty("postgresql.servant.url"), prop.getProperty("postgresql.username"), prop.getProperty("postgresql.password"));

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
    }
}
